#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:pengfeifu
@file: day_17_mysqldb_pool.py 
@version:
@time: 2021/11/08 
@email:1324734112@qq.com
@desc： 数据库连接池
@function：常用函数
"""
import MySQLdb
from MySQLdb.cursors import DictCursor
from DBUtils.PooledDB import PooledDB

import config.db_config as config


class BaseMysqlPool(object):
    def __init__(self, host, port, user, password, db_name, charset):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = db_name
        self.charset = charset
        self.conn = None
        self.cursor = None


class MysqlDBPool(BaseMysqlPool):
    """
        MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现
            获取连接对象：conn = Mysql.getConn()
            释放连接对象;conn.close()或del conn
    """
    # 连接池对象
    __pool = None

    def __init__(self, config_section="db_mysql"):
        self.conf = config.Config().get_config_content(config_section)
        super(MysqlDBPool, self).__init__(**self.conf)
        # 数据库构造函数，生成connection对象，cursor对象
        self.__connection = self.get_connection()
        self.__cursor = self.__connection.cursor()

    def get_connection(self):
        if MysqlDBPool.__pool is None:
            __pool = PooledDB(creator=MySQLdb,
                              mincached=10,
                              maxcached=20,
                              maxconnections=100,
                              host=self.host,
                              port=self.port,
                              user=self.user,
                              passwd=self.password,
                              db=self.database,
                              use_unicode=True,
                              charset=self.charset,
                              cursorclass=DictCursor)
        return __pool.connection()

    def select_one(self, sql, param=None):
        if param is None:
            count = self.__cursor.execute(sql)
        else:
            count = self.__cursor.execute(sql, param)
            # fetchone()获取到的是字典
        return self.__cursor.fetchone() if count > 0 else False

    def select_all(self, sql, param=None):
        if param is None:
            count = self.__cursor.execute(sql)
        else:
            # cursor().fetchall()获取到的是元组
            count = self.__cursor.execute(sql, param)
        return self.__cursor.fetchall() if count > 0 else False

    # 类似limit num
    def select_many(self, sql, num, param=None):
        if param is None:
            count = self.__cursor.execute(sql)
        else:
            # cursor().fetchall()获取到的是元组
            count = self.__cursor.execute(sql, param)
        return self.__cursor.fetchmany(num) if count > 0 else False

    # 批量插入/更新sql记录,values：list/turple
    def execute_many(self, sql, values):
        count = self.__cursor.executemany(sql, values)
        return count

    # 定义统一的sql操作访问接口
    def execute_sql(self, sql, param):
        return self.__cursor.execute(sql) if param is None else self.__cursor.execute(sql, param)

    # 定义sql更新函数
    def sql_update(self, sql, params):
        count = self.execute_sql(sql, params)
        return count

    # 释放资源
    def dispose(self):
        self.__cursor.close()
        self.__connection.close()

    # 提交更新
    def sql_commit(self):
        try:
            self.__connection.commit()
        except Exception:
            self.__connection.rollback()
        finally:
            self.dispose()


if __name__ == "__main__":
    db_pool_obj = MysqlDBPool("db_mysql_aliyun")
    result = db_pool_obj.select_one("select count(1) from tb_title")
    #result = db_pool_obj.sql_update("update wx_message set send_time=%s,wx_tmp=%s where wx_message_id=%s",params=[datetime.datetime.now(), "hello", "1003"])
    print(result)
    db_pool_obj.sql_commit()
